using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;

namespace Passageways.Modules.Rolodex
{
	/// <summary>
	/// Represents a container for contact information.
	/// </summary>
	[Serializable]
	public class Contact
	{
		private int m_ID;
		private int m_CategoryID;
		private string m_WorkgroupID;
		private string m_CompanyName;
		private string m_Name;
		private string m_Phone;
		private Address m_Address;
		private string m_Fax;
		private string m_LastUpdateBy;
		private DateTime m_LastUpdateDateTime;
		private string m_Email;
		private string m_Notes;
		private bool m_Deleted;
		private string m_Website;

		/// <summary>
		/// Initializes an empty instance of the <see cref="Contact"/> class.
		/// </summary>
		private Contact()
		{
			m_Address = new Address();
		}

		/// <summary>
		/// Initializes an instance of the <see cref="Contact"/> class to the contact with the given ID.
		/// </summary>
		/// <param name="id"> The ID of the contact to initialize. </param>
		public Contact(int id) : this()
		{
			string query = "SELECT * FROM Contact WHERE ID = @ID";

			SqlParameter paramID = new SqlParameter("ID", SqlDbType.Int);
			paramID.Value = id;

			//TODO: Get a reader using the query, user ReadContact() to populate this instance
		}

		/// <summary>
		/// Gets the ID of the contact.
		/// </summary>
		public int ID
		{
			get { return m_ID; }
		}

		/// <summary>
		/// Gets the ID of the category this contact is grouped into.
		/// </summary>
		public int CategoryID
		{
			get { return m_CategoryID; }
			set
			{
				m_CategoryID = value;
			}
		}

		/// <summary>
		/// Gets the ID of the workgroup for which this contact was created.
		/// </summary>
		public string WorkgroupID
		{
			get { return m_WorkgroupID; }
		}

		/// <summary>
		/// Gets or sets the name of the contact's company.
		/// </summary>
		public string CompanyName
		{
			get { return m_CompanyName; }
			set { m_CompanyName = value; }
		}

		/// <summary>
		/// Gets or sets the name of the contact.
		/// </summary>
		public string Name
		{
			get { return m_Name; }
			set { m_Name = value; }
		}

		/// <summary>
		/// Gets or sets the phone number of the contact.
		/// </summary>
		public string Phone
		{
			get { return m_Phone; }
			set { m_Phone = value; }
		}

		/// <summary>
		/// Gets or sets the address of the contact.
		/// </summary>
		public Address Address
		{
			get { return m_Address; }
		}

		/// <summary>
		/// Gets or sets the fax number of the contact.
		/// </summary>
		public string Fax
		{
			get { return m_Fax; }
			set { m_Fax = value; }
		}

		/// <summary>
		/// Gets the name of the user that last updated the contact.
		/// </summary>
		public string LastUpdateBy
		{
			get { return m_LastUpdateBy; }
		}

		/// <summary>
		/// Gets the date and time of the last time the contact was updated.
		/// </summary>
		public DateTime LastUpdateDateTime
		{
			get { return m_LastUpdateDateTime; }
		}

		/// <summary>
		/// Gets or sets the email of the contact.
		/// </summary>
		public string Email
		{
			get { return m_Email; }
			set { m_Email = value; }
		}

		/// <summary>
		/// Gets or sets notes on the contact.
		/// </summary>
		public string Notes
		{
			get { return m_Notes; }
			set { m_Notes = value; }
		}

		/// <summary>
		/// Gets a value indicating whether or not the contact is deleted.
		/// </summary>
		public bool Deleted
		{
			get { return m_Deleted; }
		}

		/// <summary>
		/// Gets or sets the website of the contact.
		/// </summary>
		public string Website
		{
			get { return m_Website; }
			set { m_Website = value; }
		}

		/// <summary>
		/// Gets a collection of all the <see cref="Contact"/> object in a category for a workgroup.
		/// </summary>
		/// <param name="categoryID"> The ID of the category to get contacts for. </param>
		/// <param name="workgroupID"> The ID of the workgroup to get contacts for. </param>
		public static ContactCollection Get(int categoryID, string workgroupID)
		{
			ContactCollection contacts = new ContactCollection();

			//TODO: fill in the guts to populate the contacts collection

			return contacts;
		}

		/// <summary>
		/// Gets a collection of <see cref="Contact"/> objects that match the search term.
		/// </summary>
		/// <param name="searchTerm"> The search term to search company and contact name for. </param>
		/// <param name="userID"> The ID of the user performing the search. </param>
		public static ContactCollection Search(string searchTerm, string userID)
		{
			ContactCollection contacts = new ContactCollection();

			string query = "SELECT * FROM Contact WHERE (Name LIKE '%' + @SearchTerm + '%') OR (CompanyName LIKE '%' + @SearchTerm + '%') ORDER BY CompanyName";

			SqlParameter paramSearchTerm = new SqlParameter("SearchTerm", SqlDbType.VarChar);
			paramSearchTerm.Value = searchTerm;

			//TODO: use the query to get the search results
				//TODO: for each record, instantiate a new contact
				//TODO: populate the contact using ReadContact
					//TODO: add the contacts to the collection
					//exclude the contact if this user is not a member of the contact's workgroup

			return contacts;
		}

		/// <summary>
		/// Gets the number if contacts in a category.
		/// </summary>
		/// <param name="categoryID"> The ID of the category for which to get the number of contacts. </param>
		/// <param name="workgroupID"> The ID of the workgroup for thich the contacts are for. The value can be a null reference. </param>
		public static int GetNumberInCategory(int categoryID, string workgroupID)
		{
			string query = "SELECT @Count = Count(*) FROM Contact WHERE (CategoryID = @CategoryID) AND (Deleted = 0)";

			SqlParameter paramCategoryID = new SqlParameter("CategoryID", SqlDbType.Int);
			paramCategoryID.Value = categoryID;

			SqlParameter paramWorkgroupID = new SqlParameter("WorkgroupID", SqlDbType.UniqueIdentifier);

			if(string.IsNullOrEmpty(workgroupID))
			{
				paramWorkgroupID.Value = DBNull.Value;
			}
			else
			{
				query += " AND (WorkgroupID = @WorkgroupID)";

				paramWorkgroupID.Value = SqlGuid.Parse(workgroupID);
			}

			SqlParameter paramCount = new SqlParameter("Count", SqlDbType.Int);
			paramCount.Direction = ParameterDirection.Output;

			//TODO: use the query to get the count

			return (int)paramCount.Value;
		}

		/// <summary>
		/// Adds a new contact.
		/// </summary>
		/// <param name="categoryID"> The ID of the category to add this contact to. </param>
		/// <param name="workgroupID"> The ID of the workgroup to add this contact for. </param>
		/// <param name="companyName"> The name of the contact's company. </param>
		/// <param name="name"> The name of the contact person. </param>
		/// <param name="phone"> The phone number of the contact. </param>
		/// <param name="address"> The address of the contact. This value can be null. </param>
		/// <param name="fax"> The fax number of the contact. This value can be null. </param>
		/// <param name="email"> The email address of the contact. This value can be null. </param>
		/// <param name="notes"> Notes on the contact. This value can be null. </param>
		/// <param name="website"> The website of the contact. This value can be null. </param>
		/// <returns> The <see cref="Contact"/> object that was just created. </returns>
		public static Contact Add(int categoryID, string workgroupID, string companyName, string name, string phone, Address address, string fax, string email, string notes, string website)
		{
			StringBuilder query = new StringBuilder();
			query.Append("INSERT INTO Contact ");
			query.Append("            (CategoryID, ");
			query.Append("             WorkgroupID, ");
			query.Append("             CompanyName, ");
			query.Append("             Name, ");
			query.Append("             Phone, ");
			query.Append("             AddressLine1, ");
			query.Append("             AddressLine2, ");
			query.Append("             City, ");
			query.Append("             State, ");
			query.Append("             Zip, ");
			query.Append("             Fax, ");
			query.Append("             Email, ");
			query.Append("             Notes, ");
			query.Append("             Website, ");
			query.Append("             LastUpdateBy, ");
			query.Append("             LastUpdateDateTime) ");
			query.Append("VALUES      (@CategoryID, ");
			query.Append("             @WorkgroupID, ");
			query.Append("             @CompanyName, ");
			query.Append("             @Name, ");
			query.Append("             @Phone, ");
			query.Append("             @AddressLine1, ");
			query.Append("             @AddressLine2, ");
			query.Append("             @City, ");
			query.Append("             @State, ");
			query.Append("             @Zip, ");
			query.Append("             @Fax, ");
			query.Append("             @Email, ");
			query.Append("             @Notes, ");
			query.Append("             @Website, ");
			query.Append("             @LastUpdateBy, ");
			query.Append("             @LastUpdateDateTime);");
			query.Append("SET @ID = @@IDENTITY;");

			SqlParameter paramID = new SqlParameter("ID", SqlDbType.Int);
			paramID.Direction = ParameterDirection.Output;

			SqlParameter paramCategoryID = new SqlParameter("CategoryID", SqlDbType.Int);
			paramCategoryID.Value = categoryID;

			SqlParameter paramWorkgroupID = new SqlParameter("WorkgroupID", SqlDbType.UniqueIdentifier);
			paramWorkgroupID.Value = SqlGuid.Parse(workgroupID);

			SqlParameter paramCompanyName = new SqlParameter("CompanyName", SqlDbType.VarChar, 150);
			paramCompanyName.Value = companyName;

			SqlParameter paramName = new SqlParameter("Name", SqlDbType.VarChar, 250);
			paramName.Value = name;

			SqlParameter paramPhone = new SqlParameter("Phone", SqlDbType.VarChar, 100);
			paramPhone.Value = phone;

			SqlParameter paramEmail = new SqlParameter("Email", SqlDbType.VarChar, 150);
			paramEmail.Value = email;

			SqlParameter paramWebsite = new SqlParameter("Website", SqlDbType.VarChar, 250);

			if(string.IsNullOrEmpty(website))
			{
				paramWebsite.Value = DBNull.Value;
			}
			else
			{
				paramWebsite.Value = website;
			}

			SqlParameter paramAddressLine1 = new SqlParameter("AddressLine1", SqlDbType.VarChar, 250);

			if((address == null) || string.IsNullOrEmpty(address.AddressLine1))
			{
				paramAddressLine1.Value = DBNull.Value;
			}
			else
			{
				paramAddressLine1.Value = address.AddressLine1;
			}

			SqlParameter paramAddressLine2 = new SqlParameter("AddressLine2", SqlDbType.VarChar, 250);

			if((address == null) || string.IsNullOrEmpty(address.AddressLine2))
			{
				paramAddressLine2.Value = DBNull.Value;
			}
			else
			{
				paramAddressLine2.Value = address.AddressLine2;
			}

			SqlParameter paramCity = new SqlParameter("City", SqlDbType.VarChar, 100);

			if((address == null) || string.IsNullOrEmpty(address.City))
			{
				paramCity.Value = DBNull.Value;
			}
			else
			{
				paramCity.Value = address.City;
			}

			SqlParameter paramState = new SqlParameter("State", SqlDbType.VarChar, 50);

			if((address == null) || string.IsNullOrEmpty(address.State))
			{
				paramState.Value = DBNull.Value;
			}
			else
			{
				paramState.Value = address.State;
			}

			SqlParameter paramZip = new SqlParameter("Zip", SqlDbType.VarChar, 25);

			if((address == null) || string.IsNullOrEmpty(address.Zip))
			{
				paramZip.Value = DBNull.Value;
			}
			else
			{
				paramZip.Value = address.Zip;
			}

			SqlParameter paramFax = new SqlParameter("Fax", SqlDbType.VarChar, 100);

			if(string.IsNullOrEmpty(fax))
			{
				paramFax.Value = DBNull.Value;
			}
			else
			{
				paramFax.Value = fax;
			}

			string lastUpdatedBy = ""; //TODO: get the user's full name from the portal session

			SqlParameter paramNotes = new SqlParameter("Notes", SqlDbType.NText);

			if(string.IsNullOrEmpty(notes))
			{
				paramNotes.Value = DBNull.Value;
			}
			else
			{
				paramNotes.Value = notes;
			}

			SqlParameter paramLastUpdateBy = new SqlParameter("LastUpdateBy", SqlDbType.VarChar, 150);
			paramLastUpdateBy.Value = lastUpdatedBy;

			DateTime lastUpdatedOn = DateTime.Now;

			SqlParameter paramLastUpdateDateTime = new SqlParameter("LastUpdateDateTime", SqlDbType.DateTime);
			paramLastUpdateDateTime.Value = lastUpdatedOn;

			//TODO: use the query to add the contact

			Contact contact = new Contact();
			contact.m_Address = address;
			contact.m_CategoryID = categoryID;
			contact.m_CompanyName = companyName;
			contact.m_Email = email;
			contact.m_Fax = fax;
			contact.m_ID = (int)paramID.Value;
			contact.m_LastUpdateBy = lastUpdatedBy;
			contact.m_LastUpdateDateTime = lastUpdatedOn;
			contact.m_Name = name;
			contact.m_Notes = notes;
			contact.m_Phone = phone;
			contact.m_Website = website;
			contact.m_WorkgroupID = workgroupID;

			return contact;
		}

		/// <summary>
		/// Commits any changes to the contact.
		/// </summary>
		public void Update()
		{
			StringBuilder query = new StringBuilder();
			query.Append("UPDATE Contact ");
			query.Append("SET    CompanyName = @CompanyName, ");
			query.Append("       Name = @Name, ");
			query.Append("       Phone = @Phone, ");
			query.Append("       AddressLine1 = @AddressLine1, ");
			query.Append("       AddressLine2 = @AddressLine2, ");
			query.Append("       City = @City, ");
			query.Append("       State = @State, ");
			query.Append("       Zip = @Zip, ");
			query.Append("       Fax = @Fax, ");
			query.Append("       Email = @Email, ");
			query.Append("       Notes = @Notes, ");
			query.Append("       Website = @Website, ");
			query.Append("       LastUpdateBy = @LastUpdateBy, ");
			query.Append("       LastUpdateDateTime = @LastUpdateDateTime, ");
			query.Append("       CategoryID = @CategoryID ");
			query.Append("WHERE  (ID = @ID)");

			SqlParameter paramID = new SqlParameter("ID", SqlDbType.Int);
			paramID.Value = m_ID;

			SqlParameter paramCompanyName = new SqlParameter("CompanyName", SqlDbType.VarChar, 150);
			paramCompanyName.Value = m_CompanyName;

			SqlParameter paramName = new SqlParameter("Name", SqlDbType.VarChar, 250);
			paramName.Value = m_Name;

			SqlParameter paramPhone = new SqlParameter("Phone", SqlDbType.VarChar, 100);
			paramPhone.Value = m_Phone;

			SqlParameter paramEmail = new SqlParameter("Email", SqlDbType.VarChar, 150);
			paramEmail.Value = m_Email;

			SqlParameter paramWebsite = new SqlParameter("Website", SqlDbType.VarChar, 250);
			
			if(string.IsNullOrEmpty(m_Website))
			{
				paramWebsite.Value = DBNull.Value;
			}
			else
			{
				paramWebsite.Value = m_Website;
			}

			SqlParameter paramAddressLine1 = new SqlParameter("AddressLine1", SqlDbType.VarChar, 250);
			
			if(string.IsNullOrEmpty(m_Address.AddressLine1))
			{
				paramAddressLine1.Value = DBNull.Value;
			}
			else
			{
				paramAddressLine1.Value = m_Address.AddressLine1;
			}

			SqlParameter paramAddressLine2 = new SqlParameter("AddressLine2", SqlDbType.VarChar, 250);

			if(string.IsNullOrEmpty(m_Address.AddressLine2))
			{
				paramAddressLine2.Value = DBNull.Value;
			}
			else
			{
				paramAddressLine2.Value = m_Address.AddressLine2;
			}

			SqlParameter paramCity = new SqlParameter("City", SqlDbType.VarChar, 100);

			if(string.IsNullOrEmpty(m_Address.City))
			{
				paramCity.Value = DBNull.Value;
			}
			else
			{
				paramCity.Value = m_Address.City;
			}

			SqlParameter paramState = new SqlParameter("State", SqlDbType.VarChar, 50);

			if(string.IsNullOrEmpty(m_Address.State))
			{
				paramState.Value = DBNull.Value;
			}
			else
			{
				paramState.Value = m_Address.State;
			}

			SqlParameter paramZip = new SqlParameter("Zip", SqlDbType.VarChar, 25);

			if(string.IsNullOrEmpty(m_Address.Zip))
			{
				paramZip.Value = DBNull.Value;
			}
			else
			{
				paramZip.Value = m_Address.Zip;
			}

			SqlParameter paramFax = new SqlParameter("Fax", SqlDbType.VarChar, 100);
			
			if(string.IsNullOrEmpty(m_Fax))
			{
				paramFax.Value = DBNull.Value;
			}
			else
			{
				paramFax.Value = m_Fax;
			}

			SqlParameter paramNotes = new SqlParameter("Notes", SqlDbType.NText);
			
			if(string.IsNullOrEmpty(m_Notes))
			{
				paramNotes.Value = DBNull.Value;
			}
			else
			{
				paramNotes.Value = m_Notes;
			}

			SqlParameter paramLastUpdateBy = new SqlParameter("LastUpdateBy", SqlDbType.VarChar, 150);
			paramLastUpdateBy.Value = ""; //TODO: Get the current user's full name from the portal session

			SqlParameter paramLastUpdateDateTime = new SqlParameter("LastUpdateDateTime", SqlDbType.DateTime);
			paramLastUpdateDateTime.Value = DateTime.Now;

			SqlParameter paramCategory = new SqlParameter("CategoryID", SqlDbType.Int);
			paramCategory.Value = CategoryID;

			//TODO: use the query to update the contact
		}

		/// <summary>
		/// Removes the contact.
		/// </summary>
		/// <param name="id"> The ID of the contact to remove. </param>
		public static void Remove(int id)
		{
			string query = "UPDATE Contact SET Deleted = 1 WHERE (ID = @ID)";

			SqlParameter paramID = new SqlParameter("ID", SqlDbType.Int);
			paramID.Value = id;

			//TODO: use the query to remove the contact
		}

		/// <summary>
		/// Reads the data from a <see cref="SqlDataReader"/> into a <see cref="Contact"/> object.
		/// </summary>
		/// <param name="reader"> A <see cref="SqlDataReader"/> containing contact data. </param>
		/// <param name="contact"> An instance of the <see cref="Contact"/> class to read the data from <paramref name="reader"/> to. </param>
		private static void ReadContact(SqlDataReader reader, Contact contact)
		{
			contact.m_ID = (int)reader["ID"];
			contact.m_CategoryID = (int)reader["CategoryID"];
			contact.m_WorkgroupID = reader["WorkgroupID"].ToString();
			contact.m_CompanyName = (string)reader["CompanyName"];
			contact.m_Name = (string)reader["Name"];
			contact.m_Phone = (string)reader["Phone"];
			contact.m_Email = (string)reader["Email"];

			object addressLine1 = reader["AddressLine1"];

			if(addressLine1 == DBNull.Value)
			{
				contact.m_Address.AddressLine1 = string.Empty;
			}
			else
			{
				contact.m_Address.AddressLine1 = (string)addressLine1;
			}

			object addressLine2 = reader["AddressLine2"];

			if(addressLine2 == DBNull.Value)
			{
				contact.m_Address.AddressLine2 = string.Empty;
			}
			else
			{
				contact.m_Address.AddressLine2 = (string)addressLine2;
			}

			object city = reader["City"];

			if(city == DBNull.Value)
			{
				contact.m_Address.City = string.Empty;
			}
			else
			{
				contact.m_Address.City = (string)city;
			}
			
			object state = reader["State"];

			if(state == DBNull.Value)
			{
				contact.m_Address.State = string.Empty;
			}
			else
			{
				contact.m_Address.State = (string)state;
			}

			object zip = reader["Zip"];

			if(zip == DBNull.Value)
			{
				contact.m_Address.Zip = string.Empty;
			}
			else
			{
				contact.m_Address.Zip = (string)zip;
			}

			object fax = reader["Fax"];

			if(fax == DBNull.Value)
			{
				contact.m_Fax = string.Empty;
			}
			else
			{
				contact.m_Fax = (string)fax;
			}

			object notes = reader["Notes"];

			if(notes == DBNull.Value)
			{
				contact.m_Notes = string.Empty;
			}
			else
			{
				contact.m_Notes = (string)notes;
			}

			object website = reader["Website"];

			if(website == DBNull.Value)
			{
				contact.m_Website = string.Empty;
			}
			else
			{
				contact.m_Website = (string)website;
			}
			
			contact.m_LastUpdateBy = (string)reader["LastUpdateBy"];
			contact.m_LastUpdateDateTime = (DateTime)reader["LastUpdateDateTime"];
			contact.m_Deleted = (bool)reader["Deleted"];
		}
	}
}